94

Beginner’s Guide to Code Algorithms

94

STEP 1—​Initialize continued

‘

‘ ExcelFileMerge Macro

‘

  Dim NewBook As Workbook

  Dim FSO As Object

  Dim filepath As String

  Dim rRange As Range

  Dim A As String, Folder As String, FileName As String

  NoData =​ 0

  ii =​ 0

  Set thisWb =​ ActiveWorkbook

  Set parentWb =​ ActiveWorkbook

  Set parentWs =​ ActiveSheet

  filepathtarget =​ ActiveWorkbook.Path & “\” & Cells(2, 2)

  RowMax =​ 100

  ColMax =​ 22

  TraceColumn =​ 100

  x =​ Dir(filepathtarget)

  FirstRow =​ Cells(4, 2)

  FirstColumn =​ Cells(5, 2)

  If FirstColumn <=​ 0 Then

    FirstColumn =​ 1

  End If

  MandatoryColumn =​ Cells(7, 2)

  If MandatoryColumn < FirstColumn Then

    MandatoryColumn =​ FirstColumn

  End If

STEP 2—​prepare target

  Do

    On Error GoTo continue

    Set fileopenTargetWb =​ Workbooks.Open(filepathtarget)

    Exit Do

continue:

    If x =​ ““ Or Cells(2, 2) =​ ““ Then

      MsgBox (“Please identify the target file”)

      filepathtarget =​ Application.GetOpenFilename

    End If

    Set fileopenTargetWb =​ Workbooks.Open(filepathtarget)

  Loop Until filepathtarget <> False

 

  filePathTargetFuture =​ fileopenTargetWb.Path & “\” & fileopenTargetWb.Name &

“-​copy.xlsx”

  fileopenTargetWb.SaveAs FileName:=​filePathTargetFuture

 

  x =​ parentWs.Cells(3, 2)

Transferring these values

from the selection screen

in the previous page to a

variable so that they can be

used in the code